<?php 
/**
 * This file is part of DomSql.
 * 
 * DomSql is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation, either version 3 of the License, or
 * (at your option) any later version.
 * 
 * DomSql is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 * 
 * You should have received a copy of the GNU General Public License
 * along with DomSql.  If not, see <http://www.gnu.org/licenses/>.
 * 
 * @link http://inditel.ee/
 * @copyright 2007-2009 Inditel Meedia OÜ
 * @author Oliver Leisalu <oliver@inditel.ee>
 */

/**
* Class to handle mysql tables.
* @package DomSql
*/ 
class DomSqlTable {
	
	/**
	* @var String DomSql connection alias
	*/
	private $connectionAlias;
	/**
	* @var String
	*/
	private $tableName;
	/**
	* @var String
	*/
	private $database;
	/**
	* @var String
	*/
	private $engine = 'InnoDb';
	/**
	* @var String
	*/
	private $characterSet = 'utf8';
	/**
	* @var String
	*/
	private $characterCollate = 'utf8_estonian_ci';
	
	/**
	* @param String $tableName
	* @param String $alias DomSql connection alias
	*/
	public function __construct( $tableName, $alias = DomSql::DEFAULT_ALIAS ) {
		$this->setTableName( $tableName );
		$this->setConnectionAlias( $alias );
	}
	
	//public function __set( $var, $value ) {
		//$this->$var = $value;
	//}
	
	public function __get( $var ) {
		return $this->$var;
	}
	
	// -------------------------------------------------------------
	// FOREIGN KEY METHODS
	// -------------------------------------------------------------
	
	/**
	* @param String $indexName
	* @param Array $fieldNames
	* @param DomSqlTable $referencingTable
	* @param Array $referencingFields
	* @param String $onChange [NO ACTION|CASCADE|SET NULL|RESTRICT]
	* @param String $onDelete [NO ACTION|CASCADE|SET NULL|RESTRICT]
	*/
	public function addForeignKey( $indexName, array $fieldNames, DomSqlTable $referencingTable, array $referencingFields, $onChange = 'NO ACTION', $onDelete = 'NO ACTION' ) {
		$onChange = strtoupper($onChange);
		$onDelete = strtoupper($onDelete);
		$indexName = strtolower($indexName);
		$possibleValues = array('NO ACTION','CASCADE','SET NULL','RESTRICT');
		if( in_array($onChange, $possibleValues ) === false ) throw new InvalidArgumentException('Unknown on change status "'.$onChange.'"');
		if( in_array($onDelete, $possibleValues ) === false ) throw new InvalidArgumentException('Unknown on delete status "'.$onDelete.'"');
		
		$query = 'ALTER TABLE '.$this->getDbTable().' ADD CONSTRAINT '.$indexName.' FOREIGN KEY '.$indexName.' ('.implode(',',$fieldNames).') REFERENCES '.$referencingTable->getDbTable().'('.implode(',',$referencingFields).') ON UPDATE '.$onChange . ' ON DELETE '.$onDelete;
		try {
			DomSql::query( $query, $this->connectionAlias );
			return true;
		} catch( RuntimeException $e ) {
			if( $this->indexExists( $indexName ) === false ) {
				throw $e;
			}
		}
		return false;
	}
	
	/**
	* Just checks if index exists. If index exists, then its possible that foreign key doesn't exist!
	* Not sure how to test if foreign key exists.
	* @param String $indexName
	*/
	public function foreignKeyExists( $indexName ) {
		return $this->indexExists( $indexName );
	}
	
	/**
	* @param String $indexName
	*/
	public function dropForeignKey( $indexName ) {
		$indexName = strtolower($indexName);
		$query = 'ALTER TABLE '.$this->getDbTable().' DROP FOREIGN KEY `'.$indexName.'`';
		try {
			DomSql::query($query, $this->connectionAlias);
			return true;
		} catch( RuntimeException $e ) {
			if( $e->getCode() != 1091 )
				throw $e;
		}
		return false;
	}
	

	// -------------------------------------------------------------
	// INDEX METHODS
	// -------------------------------------------------------------
	
	/**
	* Tries to create index on property
	* @param String $indexName
	* @param Array $fields
	* @param String $indexType [index|unique]
	* @return Boolean true if index was created!
	*/
	public function addIndex( $indexName, array $fields, $indexType ) {	
		$indexType = strtoupper($indexType);
		$indexName = strtolower($indexName);

		$query = 'ALTER TABLE '.$this->getDbTable().' ADD '.$indexType.' `'.$indexName.'` (`'.implode('`,`',$fields).'`) ';
		try {
			DomSql::query($query, $this->connectionAlias);
			return true;
		} catch( RuntimeException $e ) {
			// Index already exists.
			if( $e->getCode() != 1061)
				throw $e;
		}
		return false;
	}
	
	/**
	* @param String $indexName
	* @return Boolean true if index was dropped. false if index didnt exist.
	*/
	public function dropIndex( $indexName ) {
		$indexName = strtolower($indexName);
		$query = 'DROP INDEX `'.$indexName.'` ON  '.$this->getDbTable().'';
		try {
			DomSql::query($query, $this->connectionAlias);
			return true;
		} catch( RuntimeException $e ) {
			if( $e->getCode() != 1091 )
				throw $e;
		}
		return false;
	}

	/**
	* If you want to change index nr of fields then first drop index and then add it again. This method does not test multiple field indexes.
	* @param String $indexName
	* @param Array $fields
	* @param String $indexType [index|unique]
	* @return Boolean true if index was modified or created.
	*/
	public function changeIndex( $indexName, array $fields, $indexType ) {
		$indexType = strtoupper($indexType);
		$indexName = strtolower($indexName);
		
		$query = 'SHOW INDEX FROM '.$this->getDbTable().'';
		try {
			$create = true;
			foreach( DomSql::selectQuery( $query, $this->connectionAlias ) as $info ) {
				if( $info['Key_name'] == $indexName ) {
					$create = false;
					
					if( $indexType == 'UNIQUE' && $info['Non_unique'] == 1)
						$create = true;
					else if(  $indexType == 'INDEX' && $info['Non_unique'] == 0)
						$create = true;
					
					if( $info['Column_name'] != $fields[0] )
						$create = true;
				}
			}
			if( $create === true ) {
				$this->dropIndex( $indexName );
				return $this->addIndex( $indexName, $fields, $indexType );
			}
			
		} catch( RuntimeException $e ) {
			throw $e;
		}
		return false;
	}

	/**
	* @param String $indexName
	* @return Boolean true if index exists
	*/
	public function indexExists( $indexName ) {
		$query = 'SHOW INDEX FROM '.$this->getDbTable().'';
		try {
			foreach( DomSql::selectQuery( $query, $this->connectionAlias ) as $info ) {
				//debug($info['Key_name'].' == '.$indexName);
				if( $info['Key_name'] == $indexName ) {
				//debug('aa');
					return true;
				}
			}
		} catch( RuntimeException $e ) {
			throw $e;
		}
		return false;
	}
	
	/**
	* @param String $indexName
	* @return Array index info
	* Returns name, type
	*/
	public function getIndex( $indexName ) {
		return array_pop($this->getIndexes( $indexName ));
	}
	
	/**
	* @param String $indexName
	* @return Array indexes info.
	*/
	public function getIndexes( $indexName = NULL) {
		$query = 'SHOW INDEX FROM '.$this->getDbTable().'';
		$indexes = array();
		foreach( DomSql::selectQuery( $query, $this->connectionAlias ) as $i => $data ) {
			if( $indexName === NULL or $data['Key_name'] == $indexName ) {
				$info = array();
				$info['name'] = $data['Key_name'];
				$info['type'] = $data['Index_type'] == 'FULLTEXT' ? 'fulltext' : ($data['Non_unique'] == 1 ? 'index' : 'unique');
				$indexes[] = $info;
			}
		}
		return $indexes;
	}
	
	// -------------------------------------------------------------
	// COLUMN METHODS
	// -------------------------------------------------------------
	
	/**
	* Adds simple field to table. (text, int etc)
	* @param String $name
	* @param String $type
	* @param Boolean $null
	* @return Boolean true if field was added. false if field already exists.
	*/
	public function addColumn( $name, $type, $null = false, $end = '' ) {
		$query = 'ALTER TABLE '.$this->getDbTable().' ADD `'.$name.'` '.$type.' '.($null === true ? 'NULL' : 'NOT NULL') . ' ' . $end;	
		try {
			DomSql::query($query, $this->connectionAlias);
			return true;
		} catch( RuntimeException $e ) {
			if( $e->getCode() == 1060 ) {
				return false;
			} else {
				throw $e;
			}
		}
	}
	
	/**
	* Changes row.
	* @param String $name
	* @param String $type
	* @param Boolean $null
	* @return Boolean, true if row was changed or created
	*/
	public function changeColumn( $name, $type, $null = false ) {
		$r = DomSql::selectOneQuery('SHOW COLUMNS FROM '.$this->getDbTable().' LIKE \''.$name.'\'', NULL, $this->connectionAlias);
		if( !$r['Field'] ) {
			return $this->addColumn( $name, $type, $null );
		} else {
			$change = false;
			if( $r['Type'] != strtolower($type) )
				$change = true;
			
			if( $null === true && $r['Null'] != 'YES' )
				$change = true;
		}
		if( $change === true ) {
			try {
				$query = 'ALTER TABLE '.$this->getDbTable().' CHANGE `'.$name.'` `'.$name.'` '.$type.' '.($null === true ? 'NULL' : 'NOT NULL');
				DomSql::query($query, $this->connectionAlias);
				return true;
			} catch( RuntimeException $e ) {
				throw $e;
			}
		}
		return false;
	}
	
	/**
	* @param String $name
	* @return Boolean, true if column was dropped.
	*/
	public function dropColumn( $name ) {
		try {
			$query  = 'ALTER TABLE '.$this->getDbTable().' DROP `'.$name.'`';
			DomSql::query($query, $this->connectionAlias);
			return true;
		} catch( RuntimeException $e ) {
			if( $e->getCode() != 1091) 
				throw $e;
		}
		return false;
	}
	
	/**
	* @param String $name
	* @retrun Boolean true if row exists
	*/
	public function columnExists( $name ) {
		try {
			$r = DomSql::selectOneQuery('SHOW COLUMNS FROM '.$this->getDbTable().' LIKE \''.$name.'\'', NULL, $this->connectionAlias);
			return (boolean)$r['Field'];
		} catch( RuntimeException $e) {
			throw $e;
		}
	}
	
	/**
	* @param String $name
	* @return Array column info
	* Returns name, type, null
	*/
	public function getColumn( $name ) {
		return array_pop( $this->getColumns($name) );
	}
	
	/**
	* @param String $like
	* @return Array
	*/
	public function getColumns( $like = NULL ) {
		$info = array();
		foreach( DomSql::selectQuery('SHOW COLUMNS FROM '.$this->getDbTable().' '.($like !== NULL ? 'LIKE \''.$like.'\'' : NULL), $this->connectionAlias) as $i => $r) {
			$info[$i] = array();
			$info[$i]['name'] = $r['Field'];
			$info[$i]['type'] = $r['Type'];
			$info[$i]['null'] = $r['Null'] == 'YES';
		}
		return $info;
	}
	
	// -------------------------------------------------------------
	// TABLE METHODS
	// -------------------------------------------------------------
	
	/**
	* @return Boolean true if table was created.
	* Adds field id as Primary and auto_increment
	*/
	public function create() {
		try {
			
			$mquery  = 'CREATE TABLE '.$this->getDbTable().' ( id bigint AUTO_INCREMENT, PRIMARY KEY (id) ) ENGINE='.$this->engine.' CHARACTER SET '.$this->characterSet.' COLLATE '.$this->characterCollate;
			DomSql::query( $mquery, $this->connectionAlias );
			return true;
		} catch( RuntimeException $e ) {
			return false;
		}
	}
	
	/**
	* @return Boolean true if table is dropped
	*/
	public function drop() {
		if( $this->isEmpty() === true) {
			try {
				DomSql::query('DROP TABLE '.$this->getDbTable().' ', $this->connectionAlias);
				return true;
			} catch( RuntimeException $e ) {
				if( $e->getCode() != 1051 )
					throw $e;
			}
		}
		return false;
	}
	
	/**
	* @return Boolean true if table exists
	*/
	public function exists() {
		try {
			DomSql::query('SELECT count(*) FROM '.$this->getDbTable().' ', $this->connectionAlias);
			return true;
		} catch( RuntimeException $e ) {
			if( $e->getCode() != 1146 )
				throw $e;
		}
		return false;
	}
	
	/**
	* @return Boolean true if table is empty
	*/
	public function isEmpty() {
		try {
			$res = DomSql::selectOneQuery('SELECT count(*) as n FROM '.$this->getDbTable().' ', NULL, $this->connectionAlias);
			return $res['n'] == 0;
		} catch( RuntimeException $e ) {
			if( $e->getCode() != 1146 )
				throw $e;
		}
		return false;
	}
	
	/**
	* If table doesnt exist tries to create it.
	* @return Boolean true if table was changed or created.
	*/
	public function update() {
		$ch1 = $this->create();
		$ch2 = $this->updateCharacterSet();
		$ch3 = $this->updateEngine();
		return $ch1 or $ch2 or $ch3;
	}
	
	/**
	* Updates mysql table characterset and collate.
	* @param String $set
	* @param String $collate
	* @return Boolean true if some changes were made.
	*/
	public function updateCharacterSet( $set = NULL, $collate = NULL ) {
		if( $collate !== NULL ) {
			$this->characterSet = $set;
			$this->characterCollate = $collate;
		}
	
		$info = $this->getInfo();
		if( $info['collation'] != $this->characterCollate ) {
			DomSql::query('ALTER TABLE '.$this->getDbTable().'  DEFAULT CHARACTER SET '.$this->characterSet.' COLLATE '.$this->characterCollate.' ', $this->connectionAlias);
			DomSql::query('ALTER TABLE '.$this->getDbTable().'  CONVERT TO CHARACTER SET '.$this->characterSet.' COLLATE '.$this->characterCollate.' ', $this->connectionAlias);
			return true;
		}
		return false;
	}
	
	/**
	* Changes mysql table engine
	* @param String $newEngine
	* @return Boolean true if some changes were made.
	*/
	public function updateEngine( $newEngine = NULL ) {
		if( $newEngine !== NULL )
			$this->engine = $newEngine; 
	
		$info = $this->getInfo();
		if( $info['engine'] != $this->engine ) {
			DomSql::query('ALTER TABLE '.$this->getDbTable().' ENGINE = '.$this->engine, $this->connectionAlias );
			return true;
		}
		return false;
	}
	
	/**
	* @return Array table info
	* name, engine, collation, created, size, autoincrement
	*/
	public function getInfo() {
		$r = DomSql::selectOneQuery('SHOW TABLE STATUS LIKE \''.$this->tableName.'\'', NULL, $this->connectionAlias);
		$info = array();
		$info['name'] = $r['name'];
		$info['engine'] = $r['Engine'];
		$info['collation'] = $r['Collation'];
		$info['created'] = $r['Create_time'];
		$info['size'] = $r['Data_length'];
		$info['autoincrement'] = $r['Auto_increment'];
		return $info;
	}
	
	/**
	* Analyze table structure.
	* @TODO
	*/
	public function analyze() {
		$r = DomSql::selectOneQuery(' SELECT * FROM '.$this->getDbTable().' PROCEDURE ANALYSE ( )  ', NULL, $this->connectionAlias);
		debug($r);
	}
	
	/**
	* Truncates table. (Deletes all rows.)
	* 
	* @return void
	*/
	public function truncate() {
		DomSql::query('TRUNCATE '.$this->getDbTable().'');
	}
	
	
	// -------------------------------------------------------------
	// SETTERS AND GETTERS
	// -------------------------------------------------------------
	
	/**
	* @param String $tableName
	*/
	public function setTableName( $tableName ) {
		$this->tableName = $tableName;
	}
	/**
	* @param String $database
	*/
	public function setDatabase( $database ) {
		$this->database = $database;
	}
	/**
	 * @param String $engine [MyIsam|InnoDB]
	 */
	public function setEngine( $engine ) {
		$this->engine = $engine;
	}
	/**
	 * @return String
	 */
	public function getDbTable() {
		return ($this->database ? '`'.$this->database.'`.' : NULL ).'`'.$this->tableName.'`';
	}
	/**
	* @param String $alias
	*/
	public function setConnectionAlias( $alias ) {
		$this->connectionAlias = $alias;
	}
	
	/**
	 * @param String $database
	 * @return DomSqlTable[]
	 */
	public static function getDatabaseTables( $database ) {
		$tables = array();
		foreach( DomSql::selectQuery('SHOW TABLES FROM '.$database.'') as $table ) {
			$tables[] = new DomSqlTable( $table['Tables_in_'.strtolower($database)] );
		}
		return $tables;
	}
	
	
	
}
?>